﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Agresso.Interface.Layer.SQL
{
    public class VesselSQL
    {
        public static string MaxVesselID()
        {
            return "select MAX(vessel_id) from i_vessel_mapping";
        }
        public static string InsertVesselMappingAgr()
        {
            return @"
  INSERT INTO i_vessel_mapping
           ([vessel_name]
           ,[vessel_code]
           ,[year_of_build]
           ,[flag]
           ,[dwt]
           ,[charter_type]
           ,[size_class]
           ,[agr_description]
           ,[agr_dim_value]
           ,[agr_rel_value]
            ,created_date
            ,modified_date
            ,vessel_type
            ,client
            ,profit_centre_id
            ,vessel_sid
            ,lloyd_number)
     VALUES
           (@vessel_name
           ,@vessel_code
           ,@year_of_build
           ,@flag
           ,@dwt
            ,@charter_type
           ,@size_class
           ,@agr_description
           ,@agr_dim_value
           ,@agr_rel_value
            ,@created_date
            ,@modified_date
            ,@vessel_type
            , @client
            ,@profit_centre_id
            ,@vessel_sid
            ,@lloyd_number
            )
                        ";
        }
        public static string InsertVesselMapping()
        {
            return @"
  INSERT INTO i_vessel_mapping
           ([vessel_name]
           ,[vessel_code]
           ,[year_of_build]
           ,[flag]
           ,[dwt]
           ,[charter_type]
           ,[size_class],created_date,modified_date,vessel_type, client
)
     VALUES
          (@vessel_name
           ,@vessel_code
           ,@year_of_build
           ,@flag
           ,@dwt
            ,@charter_type
           ,@size_class,@created_date,@modified_date,@vessel_type, @client
            )
                        ";
        }

        public static string UpdateVesselMapping()
        {
            return @"
            UPDATE i_vessel_mapping SET 
           [vessel_name] = @vessel_name
           ,[vessel_code]= @vessel_code
           ,[year_of_build]= @year_of_build
           ,[flag]= @flag
           ,[dwt]= @dwt
           ,[charter_type]= @charter_type
           ,[size_class]= @size_class
            ,[agr_description] =@agr_description
           ,[agr_dim_value]=@agr_dim_value
           ,[agr_rel_value]=@agr_rel_value
            ,modified_date= @modified_date
            ,vessel_type=@vessel_type
            , client= @client
            ,profit_centre_id=@profit_centre_id
            WHERE vessel_id = @vessel_id";
        }
        public static string UpdateVesselProfitCentreMapping()
        {
            return @"
            UPDATE i_vessel_mapping SET 
            profit_centre_id=@profit_centre_id
            WHERE vessel_id = @vessel_id";
        }

        public static string UpdateVesselAgressoMapping()
        {
            return @"
            UPDATE i_vessel_mapping SET 
            [agr_description] =@agr_description
           ,[agr_dim_value]=@agr_dim_value
           ,[agr_rel_value]=@agr_rel_value
            ,modified_date= @modified_date
            , client= @client
            WHERE vessel_id = @vessel_id";
        }

        public static string GetVesselMappingByVesselName()
        {
            return @"select * from i_vessel_mapping where vessel_name=@vessel_name ";
        }

        public static string GetVesselMappingByVesselCode()
        {
            return @"select * from i_vessel_mapping where vessel_code=@vessel_code ";
        }

        public static string GetVesselMappingByVesselSID()
        {
            return @"select * from i_vessel_mapping where vessel_sid=@vessel_sid ";
        }

        public static string GetVesselMappingByVesselSIDandProfitCentre()
        {
            return @"select * from i_vessel_mapping where vessel_sid=@vessel_sid and profit_centre_id=@profit_centre_id";
        }

        public static string GetVesselMappingByAgrDimvalNameAndClient()
        {
            return @"select * from i_vessel_mapping where agr_dim_value=@agr_dim_value and agr_description=@agr_description 
                and client= @client and agr_rel_value=@agr_rel_value ";
        }
         public static string GetAllVesselMapping()
        {
            return @"select * from i_vessel_mapping order by vessel_name";
         }
         public static string GetVesselMappingByProfitCentreIDAndVesselName()
         {
             return @"select * from i_vessel_mapping where vessel_name=@vessel_name and profit_centre_id=@profit_centre_id and client = @client";
         }
         public static string GetVesselMappingByDimVal()
         {
             return @"select * from i_vessel_mapping where agr_dim_value=@agr_dim_value";
         }
         public static string GetAllVesselMappingByProfitCentreID()
         {
             return @"select * from i_vessel_mapping where profit_centre_id=@profit_centre_id  order by vessel_name";
         }
         public static string GetAllVesselMappingLike()
         {
             return @"select * from i_vessel_mapping where  
                ([vessel_name]
                +[vessel_code]
                +[year_of_build]
                +[flag]
                +[dwt]
                +[charter_type]
                +[size_class]
                +[vessel_type]
                +[agr_description]
                +[agr_dim_value]
                +[agr_rel_value]
                +[client]) like '%' + @vessel + '%'
                order by vessel_name";
         }
         public static string GetAllVesselMappingByDimValueAndClient()
         {
             return @"select * from i_vessel_mapping where  
              [agr_dim_value] = @agr_dim_value and client=@client";
         }
        public static string GetAllSoftmarVessel()
        {
            return @"
                Select DISTINCT Vessel.Name vessel_name,
                VAVesselData.VesselCode vessel_Code,
                Vessel.BuiltYear year_of_build,
                vVesselFlagFullNameSrch.FlagFullName flag,
                Vessel.DwtHigh dwt,
                [vVMHeadOpfix].VMHeadType charter_type,
                VesselSizeClass.SizeClass size_class,
                Vessel.DisplayType Vessel_Type
                 FROM VESSEL JOIN VAVESSELDATA ON VESSEL.ID = VAVESSELDATA.VESSEL 
                JOIN VESSELTYPE ON VESSELTYPE.DISPLAYTYPE=VESSEL.DISPLAYTYPE 
                LEFT OUTER JOIN VVESSELFLAGFULLNAMESRCH ON VESSEL.FLAG = VVESSELFLAGFULLNAMESRCH.CODE 
                JOIN [vVMHeadOpfix] on [vVMHeadOpfix].VesselName = vessel.Name
                CROSS JOIN VESSELSIZECLASS WHERE (Vessel.Name is NOT NULL) AND 
                ISNULL(Vessel.SizeClass,'') = ISNULL(VesselSizeClass.SizeClassDisp,'') ";
        }

        public static string GetSoftmarVesselMasterByName()
        {
            return @"Select DISTINCT Vessel.Name vessel_name,
                VAVesselData.VesselCode vessel_Code,
                Vessel.BuiltYear year_of_build,
                vVesselFlagFullNameSrch.FlagFullName flag,
                Vessel.DwtHigh dwt,
                [vVMHeadOpfix].VMHeadType charter_type,
                VesselSizeClass.SizeClass Size_Class,
                Vessel.DisplayType Vessel_Type,
                Vessel.ID vessel_sid,
                Vessel.LloydsNumber lloyd_number
                 FROM VESSEL JOIN VAVESSELDATA ON VESSEL.ID = VAVESSELDATA.VESSEL 
                JOIN VESSELTYPE ON VESSELTYPE.DISPLAYTYPE=VESSEL.DISPLAYTYPE 
                LEFT OUTER JOIN VVESSELFLAGFULLNAMESRCH ON VESSEL.FLAG = VVESSELFLAGFULLNAMESRCH.CODE 
                LEFT OUTER JOIN  [vVMHeadOpfix] on [vVMHeadOpfix].VesselName = vessel.Name
                CROSS JOIN VESSELSIZECLASS 
                WHERE (Vessel.Name is NOT NULL) AND ISNULL(Vessel.SizeClass,'') = ISNULL(VesselSizeClass.SizeClassDisp,'') 
                AND VEssel.Name = @vessel_name";
        }

        public static string GetSoftmarVesselMasterByID()
        {
            return @"Select DISTINCT Vessel.Name vessel_name,
                VAVesselData.VesselCode vessel_Code,
                Vessel.BuiltYear year_of_build,
                vVesselFlagFullNameSrch.FlagFullName flag,
                Vessel.DwtHigh dwt,
                [vVMHeadOpfix].VMHeadType charter_type,
                VesselSizeClass.SizeClass Size_Class,
                Vessel.DisplayType Vessel_Type,
                Vessel.ID vessel_sid,
                Vessel.LloydsNumber lloyd_number
                 FROM VESSEL JOIN VAVESSELDATA ON VESSEL.ID = VAVESSELDATA.VESSEL 
                JOIN VESSELTYPE ON VESSELTYPE.DISPLAYTYPE=VESSEL.DISPLAYTYPE 
                LEFT OUTER JOIN VVESSELFLAGFULLNAMESRCH ON VESSEL.FLAG = VVESSELFLAGFULLNAMESRCH.CODE 
                LEFT OUTER JOIN  [vVMHeadOpfix] on [vVMHeadOpfix].VesselName = vessel.Name
                CROSS JOIN VESSELSIZECLASS 
                WHERE (Vessel.Name is NOT NULL) AND ISNULL(Vessel.SizeClass,'') = ISNULL(VesselSizeClass.SizeClassDisp,'') 
                AND Vessel.ID = @vessel_sid";
        }

        public static string GetAgressoVesselByVesselName()
        {
            return @"select 1 from agldimvalue where attribute_id = 'P5A' and description = @vessel_name";
        }

        public static string DeleteVesselMapping()
        {
            return @"delete from i_vessel_mapping where vessel_id = @vessel_id";
        }

        public static string FindVesselByCode()
        {
            return "select count(*) c from i_vessel_mapping where vessel_code = @vessel_code";
        }
    }
}
